﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using MySql.Data.MySqlClient;
using Org.BouncyCastle.Crypto.Engines;

namespace Textbook
{
    public class SQL
    {
        static string url = @"server=localhost;Allow User Variables=True;database=textbook; uid=root;pwd=123456;Character Set=utf8;";

        public MySqlConnection getConnection()
        {
            MySqlConnection conn;
            try
            {
                conn = new MySqlConnection(url);
                conn.Open();
                return conn;
            }
            catch (Exception ex)
            {
                return null;
            }
        }

        MySqlDataAdapter adapter;

        MySqlDataReader reader;

        DataSet ds;

        MySqlConnection conn;

        MySqlCommand comm;

        string sql;

        public Students stuLogin(string id, string pass)
        {
            Students students = new Students();
            students.S_id = "";
            try
            {
                conn = getConnection();
                sql = "select s_id, s_pass, s_name from students where s_id=@id and s_pass=@pass";
                comm = new MySqlCommand(sql, conn);
                comm.Parameters.AddWithValue("id", id);
                comm.Parameters.AddWithValue("pass", pass);
                reader = comm.ExecuteReader();
                if (reader.Read())
                {
                    students.S_id = reader["s_id"].ToString();
                    students.S_name = reader["s_name"].ToString();
                    students.S_pass = reader["s_pass"].ToString();
                }
                return students;
            }
            catch (Exception ex)
            {
                return students;
            }
            finally
            {
                conn.Close();
            }
        }

        public Teacher teaLogin(string id, string pass)
        {
            Teacher teacher = new Teacher();
            teacher.T_id = "";
            try
            {
                conn = getConnection();
                sql = "select t_id, t_pass, t_name from teacher where t_id=@id and t_pass=@pass";
                comm = new MySqlCommand(sql, conn);
                comm.Parameters.AddWithValue("id", id);
                comm.Parameters.AddWithValue("pass", pass);
                reader = comm.ExecuteReader();
                if (reader.Read())
                {
                    teacher.T_id = reader["t_id"].ToString();
                    teacher.T_name = reader["t_name"].ToString();
                    teacher.T_pass = reader["t_pass"].ToString();
                }
                return teacher;
            }
            catch (Exception ex)
            {
                return teacher;
            }
            finally
            {
                conn.Close();
            }
        }

        public Issue issLogin(string id, string pass)
        {
            Issue issue = new Issue();
            issue.I_id = "";
            try
            {
                conn = getConnection();
                sql = "select i_id, i_pass, i_name from issue where i_id=@id and i_pass=@pass";
                comm = new MySqlCommand(sql, conn);
                comm.Parameters.AddWithValue("id", id);
                comm.Parameters.AddWithValue("pass", pass);
                reader = comm.ExecuteReader();
                if (reader.Read())
                {
                    issue.I_id = reader["i_id"].ToString();
                    issue.I_name = reader["i_name"].ToString();
                    issue.I_pass = reader["i_pass"].ToString();
                }
                return issue;
            }
            catch (Exception ex)
            {
                return issue;
            }
            finally
            {
                conn.Close();
            }
        }

        public Procure proLogin(string id, string pass)
        {
            Procure procure = new Procure();
            procure.P_id = "";
            try
            {
                conn = getConnection();
                sql = "select p_id, p_pass, p_name from procure where p_id=@id and p_pass=@pass";
                comm = new MySqlCommand(sql, conn);
                comm.Parameters.AddWithValue("id", id);
                comm.Parameters.AddWithValue("pass", pass);
                reader = comm.ExecuteReader();
                if (reader.Read())
                {
                    procure.P_id = reader["p_id"].ToString();
                    procure.P_name = reader["p_name"].ToString();
                    procure.P_pass = reader["p_pass"].ToString();
                }
                return procure;
            }
            catch (Exception ex)
            {
                return procure;
            }
            finally
            {
                conn.Close();
            }
        }

        public List<Book> getBookList()
        {
            List<Book> list = new List<Book>();
            try
            {
                conn = getConnection();
                sql = "select book_id,b_name,b_num,b_price from book";
                comm = new MySqlCommand (sql, conn);
                reader = comm.ExecuteReader();
                while (reader.Read())
                {
                    Book b = new Book();
                    b.B_name = reader["b_name"].ToString() ;
                    b.B_price = decimal.Parse(reader["b_price"].ToString());
                    b.B_num = int.Parse(reader["b_num"].ToString());
                    b.Book_id = int.Parse(reader["book_id"].ToString());
                    list.Add(b);
                }
                return list;
            }
            catch (Exception ex)
            {
                return list;
            }
            finally { 
                conn.Close(); 
            }
        }

        public bool insertBill(Bill bill)
        {
            try
            {
                conn = getConnection();
                sql = "insert into bill(b_id,book_id,b_num,b_money,b_time,t_id,s_id)" +
                    "values(@b_id,@book_id,@b_num,@b_money,@b_time,@t_id,@s_id)";
                comm = new MySqlCommand(sql, conn);
                comm.Parameters.AddWithValue("b_id", bill.B_id);
                comm.Parameters.AddWithValue("book_id", bill.Book_id);
                comm.Parameters.AddWithValue("b_num", bill.B_num);
                comm.Parameters.AddWithValue("b_money", bill.B_money);
                comm.Parameters.AddWithValue("b_time", bill.B_time);
                comm.Parameters.AddWithValue("t_id", bill.T_id);
                comm.Parameters.AddWithValue("s_id", bill.S_id);
                return comm.ExecuteNonQuery()>0;
            }
            catch (Exception ex)
            {
                return false;
            }
            finally
            {
                conn.Close();
            }
        }

        public bool insertLess(Less less)
        {
            try
            {
                conn = getConnection();
                sql = "insert into less(l_id,book_id,l_num,l_time)" +
                    "values(@l_id,@book_id,@l_num,@l_time)";
                comm = new MySqlCommand(sql, conn);
                comm.Parameters.AddWithValue("l_id", less.L_id);
                comm.Parameters.AddWithValue("book_id", less.Book_id);
                comm.Parameters.AddWithValue("l_num", less.L_num);
                comm.Parameters.AddWithValue("l_time", less.L_time);
                return comm.ExecuteNonQuery() > 0;
            }
            catch (Exception ex)
            {
                return false;
            }
            finally
            {
                conn.Close();
            }
        }

        public DataSet selectBillById(string id, bool isStu, int state)
        {
            try
            {
                conn = getConnection();
                sql = "select b_id 订单号, book_id 书籍编号, b_name 书名, bill.b_num 数量,b_money 金额, " +
                    "b_time 时间 from bill JOIN book USING(book_id) where t_id=@id and b_state=@state";
                if (isStu)
                {
                    sql = "select b_id 订单号, book_id 书籍编号, b_name 书名, bill.b_num 数量,b_money 金额, " +
                    "b_time 时间 from bill JOIN book USING(book_id) where s_id=@id and b_state=@state";
                }
                comm = new MySqlCommand(sql, conn);
                comm.Parameters.AddWithValue("id", id);
                comm.Parameters.AddWithValue("state", state);
                adapter = new MySqlDataAdapter(comm);
                ds = new DataSet();
                adapter.Fill(ds);
                return ds;
            }
            catch (Exception ex)
            {
                return ds;
            }
            finally
            {
                conn.Close();
            }
        }

        public DataSet selectBill(int state)
        {
            try
            {
                conn = getConnection();
                sql = "select b_id 订单号, book_id 书籍编号, b_name 书名, bill.b_num 数量,b_money 金额, " +
                    "b_time 时间 from bill JOIN book USING(book_id) where b_state=@state";
                comm = new MySqlCommand(sql, conn);
                comm.Parameters.AddWithValue("state", state);
                adapter = new MySqlDataAdapter(comm);
                ds = new DataSet();
                adapter.Fill(ds);
                return ds;
            }
            catch (Exception ex)
            {
                return ds;
            }
            finally
            {
                conn.Close();
            }
        }

        public DataSet selectLess(int state)
        {
            try
            {
                conn = getConnection();
                sql = "select l_id 缺书单号, book_id 书籍编号, b_name 书名, l_num 数量, " +
                    "l_time 时间 from less JOIN book USING(book_id) where l_state=@state";
                comm = new MySqlCommand(sql, conn);
                comm.Parameters.AddWithValue("state", state);
                adapter = new MySqlDataAdapter(comm);
                ds = new DataSet();
                adapter.Fill(ds);
                return ds;
            }
            catch (Exception ex)
            {
                return ds;
            }
            finally
            {
                conn.Close();
            }
        }

        public DataSet selectBook()
        {
            try
            {
                conn = getConnection();
                sql = "select book_id 书籍编号, b_name 书名, b_num 数量, b_price 价格 from book";
                comm = new MySqlCommand(sql, conn);
                adapter = new MySqlDataAdapter(comm);
                ds = new DataSet();
                adapter.Fill(ds);
                return ds;
            }
            catch (Exception ex)
            {
                return ds;
            }
            finally
            {
                conn.Close();
            }
        }

        public DataSet selectWarehouse()
        {
            try
            {
                conn = getConnection();
                sql = "SELECT w_id 编号,b_name 书名, b_num 现有库存, b_price 单价, " +
                    "w_num 进出库, w_time 时间 FROM warehouse JOIN book USING(book_id)";
                comm = new MySqlCommand(sql, conn);
                adapter = new MySqlDataAdapter(comm);
                ds = new DataSet();
                adapter.Fill(ds);
                return ds;
            }
            catch (Exception ex)
            {
                return ds;
            }
            finally
            {
                conn.Close();
            }
        }

        public bool updateLessState(string l_id, int book_id, int state)
        {
            try
            {
                conn = getConnection();
                sql = "update less set l_state=@state where l_id=@l_id and book_id=@book_id";
                comm = new MySqlCommand(sql, conn);
                comm.Parameters.AddWithValue("l_id", l_id);
                comm.Parameters.AddWithValue("book_id", book_id);
                comm.Parameters.AddWithValue("state", state);
                return comm.ExecuteNonQuery() > 0;
            }
            catch (Exception ex)
            {
                return false;
            }
            finally
            {
                conn.Close();
            }
        }

        public bool updateLessState(string l_id, int book_id, int num, int state)
        {
            MySqlTransaction transaction = null;
            try
            {
                conn = getConnection();
                transaction = conn.BeginTransaction();
                sql = "update less set l_state=@state where l_id=@l_id and book_id=@book_id";
                comm = new MySqlCommand(sql, conn);
                comm.Parameters.AddWithValue("l_id", l_id);
                comm.Parameters.AddWithValue("book_id", book_id);
                comm.Parameters.AddWithValue("state", state);
                if(comm.ExecuteNonQuery() > 0 && insertWare(book_id, num, true ,conn)
                    && updateBookNum(book_id, num, true, conn))
                {
                    transaction.Commit();
                    return true;
                }
                else
                {
                    transaction.Rollback();
                    return false;
                }
            }
            catch (Exception ex)
            {
                transaction.Rollback();
                return false;
            }
            finally
            {
                conn.Close();
            }
        }


        public bool updateBillandBook(string b_id, int book_id, int num, int state)
        {
            MySqlTransaction transaction = null;
            try
            {
                conn = getConnection();
                transaction = conn.BeginTransaction();
                sql = "update bill set b_state=@state where b_id=@b_id and book_id=@book_id";
                comm = new MySqlCommand(sql, conn);
                comm.Parameters.AddWithValue("b_id", b_id);
                comm.Parameters.AddWithValue("book_id", book_id);
                comm.Parameters.AddWithValue("state", state);
                if(comm.ExecuteNonQuery() > 0 && 
                    updateBookNum(book_id, num, false, conn) &&
                    insertWare(book_id, num, false, conn)
                    )
                {
                    transaction.Commit();
                    return true;
                }
                else
                {
                    transaction.Rollback();
                    return false;
                }
            }
            catch (Exception ex)
            {
                transaction.Rollback();
                return false;
            }
            finally
            {
                conn.Close();
            }
        }

        public bool updateBillState(string b_id, int book_id, int state)
        {
            try
            {
                conn = getConnection();
                sql = "update bill set b_state=@state where b_id=@b_id and book_id=@book_id";
                comm = new MySqlCommand(sql, conn);
                comm.Parameters.AddWithValue("b_id", b_id);
                comm.Parameters.AddWithValue("book_id", book_id);
                comm.Parameters.AddWithValue("state", state);
                return comm.ExecuteNonQuery() > 0;
            }
            catch (Exception ex)
            {
                return false;
            }
            finally
            {
                conn.Close();
            }
        }

        public bool updateBookNum(int book_id, int num, bool isAdd, MySqlConnection con)
        {
            try
            {
                sql = "update book set b_num=b_num-@num where book_id=@book_id";
                if (isAdd)
                {
                    sql = "update book set b_num=b_num+@num where book_id=@book_id";
                }
                comm = new MySqlCommand(sql, con);
                comm.Parameters.AddWithValue("book_id", book_id);
                comm.Parameters.AddWithValue("num", num);
                return comm.ExecuteNonQuery() > 0;
            }
            catch (Exception ex)
            {
                return false;
            }
        }

        public bool insertWare(int book_id, int w_num, bool isAdd, MySqlConnection con)
        {
            string w_id = Utils.getTimeTicks();
            try
            {
                sql = "insert warehouse(w_id,book_id,w_num,w_time)" +
                    "values(@w_id,@book_id,@w_num,@w_time)";
                if (!isAdd)
                {
                    w_num = -w_num;
                }
                comm = new MySqlCommand(sql, con);
                comm.Parameters.AddWithValue("book_id", book_id);
                comm.Parameters.AddWithValue("w_id", w_id);
                comm.Parameters.AddWithValue("w_num", w_num);
                comm.Parameters.AddWithValue("w_time", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
                return comm.ExecuteNonQuery() > 0;
            }
            catch (Exception ex)
            {
                return false;
            }
        }
    }
}
